/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package com.informet.ipd.modelo.dao;

import com.informet.ipd.modelo.vo.MarcoLogicoVO;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import javax.swing.JOptionPane;

/**
 *
 * @author lover
 */
public class MarcoLogicoDao extends DBase{



    Connection conn = null;

    public MarcoLogicoDao()  {
        super();
    }


    private void setMarcoLogicoFin(MarcoLogicoVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_FINPRG_PK(rs.getInt("NUM_FINPRG_PK"));
        bean.setNUM_METAFINPRG_PK(rs.getInt("NUM_METAFINPRG_PK"));
        bean.setTX_DESCFINPRG(rs.getString("TX_DESCFINPRG"));
        bean.setNUM_CANTMETAFINPRG(rs.getInt("NUM_CANTMETAFINPRG"));
        bean.setTX_DESCMETAFINPRG(rs.getString("TX_DESCMETAFINPRG"));
        bean.setTX_TIPOMETAFINPRG(rs.getInt("TX_TIPOMETAFINPRG"));
        bean.setNUM_CANTMETAFINPRGC(rs.getInt("NUM_CANTMETAFINPRGC"));
        bean.setTX_FUEMETAFINPPRG(rs.getString("TX_FUENTEFINPRG"));
    }

    private void setMarcoLogicoFinP(MarcoLogicoVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_FINPRG_PK(rs.getInt("NUM_FINPRG_PK"));
        bean.setNUM_METAFINPRG_PK(rs.getInt("NUM_METAFINPRG_PK"));
        bean.setTX_DESCFINPRG(rs.getString("TX_DESCFINPRG"));
        bean.setNUM_CANTMETAFINPRG(rs.getInt("NUM_CANTMETAFINPRG"));
        bean.setTX_DESCMETAFINPRG(rs.getString("TX_DESCMETAFINPRG"));
        bean.setTX_TIPOMETAFINPRG(rs.getInt("TX_TIPOMETAFINPRG"));
        bean.setNUM_CANTMETAFINPRGC(rs.getInt("NUM_CANTMETAFINPRGC"));
        bean.setTX_FUEMETAFINPPRG(rs.getString("TX_FUENTEFINPRG"));
        bean.setNUM_PYINV_PK(rs.getInt("NUM_CODPRG_PK"));
    }

    private void setMarcoLogicoFinI(MarcoLogicoVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_FINPRG_PK(rs.getInt("NUM_FINPRY_PK"));
        bean.setNUM_METAFINPRG_PK(rs.getInt("NUM_METAFINPRY_PK"));
        bean.setTX_DESCFINPRG(rs.getString("TX_DESCFINPRY"));
        bean.setNUM_CANTMETAFINPRG(rs.getInt("NUM_CANTMETAFINPRY"));
        bean.setTX_DESCMETAFINPRG(rs.getString("TX_DESCMETAFINPRY"));
        bean.setTX_TIPOMETAFINPRG(rs.getInt("TX_TIPOMETAFINPRY"));
        bean.setNUM_CANTMETAFINPRGC(rs.getInt("NUM_CANTMETAFINPRYC"));
        bean.setTX_FUEMETAFINPPRG(rs.getString("TX_FUENTEFINPRY"));
        bean.setNUM_PYINV_PK(rs.getInt("NUM_PYINV_PK"));
    }

    private void setMarcoLogicoProgramaI(MarcoLogicoVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_PYINV_PK(rs.getInt("NUM_PYINV_PK"));
        bean.setTX_NOMBREPYI(rs.getString("TX_NOMBREPYI"));
    }

    private void setMarcoLogicoPrograma(MarcoLogicoVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_PYINV_PK(rs.getInt("NUM_CODPRG_PK"));
        bean.setTX_NOMBREPYI(rs.getString("TX_TITULOPRG"));
    }

    private void setMarcoLogicoProp(MarcoLogicoVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_PROPPRG_PK(rs.getInt("NUM_PROPPRG_PK"));
        bean.setNUM_METAPROPPRG_PK(rs.getInt("NUM_METAPROPPRG_PK"));
        bean.setTX_DESCPROPPRG(rs.getString("TX_DESCPROPPRG"));
        bean.setNUM_CANTMETAPROPPRG(rs.getInt("NUM_CANTMETAPROPPRG"));
        bean.setTX_DESCMETAPROPPRG(rs.getString("TX_DESCMETAPROPPRG"));
        bean.setTX_TIPOMETAPROPPRG(rs.getInt("TX_TIPOMETAPROPPRG"));
        bean.setNUM_CANTMETAPROPPRGC(rs.getInt("NUM_CANTMETAPROPPRGC"));
        bean.setTX_FUENTEPROPPRG(rs.getString("TX_FUENTEPROPPRG"));

    }

    private void setMarcoLogicoPropP(MarcoLogicoVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_PROPPRG_PK(rs.getInt("NUM_PROPPRG_PK"));
        bean.setNUM_METAPROPPRG_PK(rs.getInt("NUM_METAPROPPRG_PK"));
        bean.setTX_DESCPROPPRG(rs.getString("TX_DESCPROPPRG"));
        bean.setNUM_CANTMETAPROPPRG(rs.getInt("NUM_CANTMETAPROPPRG"));
        bean.setTX_DESCMETAPROPPRG(rs.getString("TX_DESCMETAPROPPRG"));
        bean.setTX_TIPOMETAPROPPRG(rs.getInt("TX_TIPOMETAPROPPRG"));
        bean.setNUM_CANTMETAPROPPRGC(rs.getInt("NUM_CANTMETAPROPPRGC"));
        bean.setTX_FUENTEPROPPRG(rs.getString("TX_FUENTEPROPPRG"));
        bean.setNUM_PYINV_PK(rs.getInt("NUM_CODPRG_PK"));

    }

    private void setMarcoLogicoPropI(MarcoLogicoVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_PROPPRG_PK(rs.getInt("NUM_PROPPYINV_PK"));
        bean.setNUM_METAPROPPRG_PK(rs.getInt("NUM_METAPROPPRY_PK"));
        bean.setTX_DESCPROPPRG(rs.getString("TX_DESCPROPPRY"));
        bean.setNUM_CANTMETAPROPPRG(rs.getInt("NUM_CANTMETAPROPPRY"));
        bean.setTX_DESCMETAPROPPRG(rs.getString("TX_DESCMETAPROPPRY"));
        bean.setTX_TIPOMETAPROPPRG(rs.getInt("TX_TIPOMETAPROPPRY"));
        bean.setNUM_CANTMETAPROPPRGC(rs.getInt("NUM_CANTMETAPROPPRYC"));
        bean.setTX_FUENTEPROPPRG(rs.getString("TX_FUENTEPROPPRY"));
        bean.setNUM_PYINV_PK(rs.getInt("NUM_PYINV_PK"));

    }

    private void setMarcoLogicoComp(MarcoLogicoVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_COMPPRG_PK(rs.getInt("NUM_COMPPRG_PK"));
        bean.setNUM_METACOMPPRG_PK(rs.getInt("NUM_METACOMPPRG"));
        bean.setTX_DESCCOMPPRG(rs.getString("TX_DESCCOMPPRG"));
        bean.setNUM_CANTMETACOMPPRG(rs.getInt("NUM_CANTMETACOMPPRG"));
        bean.setTX_DESCMETACOMPPRG(rs.getString("TX_DESCMETACOMPPRG"));
        bean.setTX_TIPOMETACOMPPRG(rs.getInt("TX_TIPOMETACOMPPRG"));
        bean.setNUM_CANTMETACOMPPRGC(rs.getInt("NUM_CANTMETACOMPPRGC"));
        bean.setTX_FUENTECOMPPRG(rs.getString("TX_FUENTECOMPPRG"));


    }

    private void setMarcoLogicoCompP(MarcoLogicoVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_COMPPRG_PK(rs.getInt("NUM_COMPPRG_PK"));
        bean.setNUM_METACOMPPRG_PK(rs.getInt("NUM_METACOMPPRG"));
        bean.setTX_DESCCOMPPRG(rs.getString("TX_DESCCOMPPRG"));
        bean.setNUM_CANTMETACOMPPRG(rs.getInt("NUM_CANTMETACOMPPRG"));
        bean.setTX_DESCMETACOMPPRG(rs.getString("TX_DESCMETACOMPPRG"));
        bean.setTX_TIPOMETACOMPPRG(rs.getInt("TX_TIPOMETACOMPPRG"));
        bean.setNUM_CANTMETACOMPPRGC(rs.getInt("NUM_CANTMETACOMPPRGC"));
        bean.setTX_FUENTECOMPPRG(rs.getString("TX_FUENTECOMPPRG"));
        bean.setNUM_PYINV_PK(rs.getInt("NUM_CODPRG_PK"));

    }

    private void setMarcoLogicoCompI(MarcoLogicoVO bean, ResultSet rs) throws SQLException {
        bean.setNUM_COMPPRG_PK(rs.getInt("NUM_COMPPYINV_PK"));
        bean.setNUM_METACOMPPRG_PK(rs.getInt("NUM_METACOMPPRY"));
        bean.setTX_DESCCOMPPRG(rs.getString("TX_DESCCOMPPYINV"));
        bean.setNUM_CANTMETACOMPPRG(rs.getInt("NUM_CANTMETACOMPPRY"));
        bean.setTX_DESCMETACOMPPRG(rs.getString("TX_DESCMETACOMPPRY"));
        bean.setTX_TIPOMETACOMPPRG(rs.getInt("TX_TIPOMETACOMPPRY"));
        bean.setNUM_CANTMETACOMPPRGC(rs.getInt("NUM_CANTMETACOMPPRYC"));
        bean.setTX_FUENTECOMPPRG(rs.getString("TX_FUENTECOMPPYINV"));
        bean.setNUM_PYINV_PK(rs.getInt("NUM_PYINV_PK"));


    }


    public List getlistMarcoLogicoFin() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MarcoLogicoVO> lista = new ArrayList<MarcoLogicoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "SELECT dbo.ME_FINPRG.NUM_FINPRG_PK,dbo.ME_METAFINPRG.NUM_METAFINPRG_PK,ME_FINPRG.TX_DESCFINPRG,ME_METAFINPRG.NUM_CANTMETAFINPRG,ME_METAFINPRG.TX_DESCMETAFINPRG,ME_METAFINPRG.TX_TIPOMETAFINPRG,ME_METAFINPRG.NUM_CANTMETAFINPRGC, ME_FINPRG.TX_FUENTEFINPRG  FROM ME_FINPRG INNER JOIN ME_METAFINPRG ON ME_FINPRG.NUM_FINPRG_PK=ME_METAFINPRG.NUM_FINPRG_PK";
            rs = stmt.executeQuery(sql);
            MarcoLogicoVO bean = null;
            while (rs.next()) {
                bean = new MarcoLogicoVO();
                setMarcoLogicoFin(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

    public List getlistMarcoLogicoFin(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MarcoLogicoVO> lista = new ArrayList<MarcoLogicoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
             pstmt = conn.prepareStatement(sql);
            sql = "SELECT ME_PROGRAMA.NUM_CODPRG_PK,dbo.ME_FINPRG.NUM_FINPRG_PK,dbo.ME_METAFINPRG.NUM_METAFINPRG_PK,ME_FINPRG.TX_DESCFINPRG,ME_METAFINPRG.NUM_CANTMETAFINPRG,ME_METAFINPRG.TX_DESCMETAFINPRG,ME_METAFINPRG.TX_TIPOMETAFINPRG,ME_METAFINPRG.NUM_CANTMETAFINPRGC, ME_FINPRG.TX_FUENTEFINPRG  FROM ME_FINPRG inner JOIN ME_METAFINPRG ON ME_FINPRG.NUM_FINPRG_PK=ME_METAFINPRG.NUM_FINPRG_PK inner JOIN ME_PROGRAMA ON ME_PROGRAMA.NUM_CODPRG_PK=ME_FINPRG.NUM_CODPRG_PK WHERE ME_PROGRAMA.NUM_CODPRG_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            MarcoLogicoVO bean = null;
            while (rs.next()) {
                bean = new MarcoLogicoVO();
                setMarcoLogicoFinP(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }



    //POR REALIZARRRRR
    public List getlistMarcoLogicoFinI(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MarcoLogicoVO> lista = new ArrayList<MarcoLogicoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            pstmt = conn.prepareStatement(sql);
            sql = "SELECT ME_PYINV.NUM_PYINV_PK, isnull(dbo.ME_FINPYINV.NUM_FINPRY_PK,0) as NUM_FINPRY_PK,isnull(dbo.ME_METAFINPYINV.NUM_METAFINPRY_PK,0) as NUM_METAFINPRY_PK,isnull(ME_FINPYINV.TX_DESCFINPRY,'') as TX_DESCFINPRY,isnull(ME_METAFINPYINV.NUM_CANTMETAFINPRY,0) as NUM_CANTMETAFINPRY,isnull(ME_METAFINPYINV.TX_DESCMETAFINPRY,'') as TX_DESCMETAFINPRY,isnull(ME_METAFINPYINV.TX_TIPOMETAFINPRY,0) as TX_TIPOMETAFINPRY,isnull(ME_METAFINPYINV.NUM_CANTMETAFINPRYC,0) as NUM_CANTMETAFINPRYC, isnull(ME_FINPYINV.TX_FUENTEFINPRY,'') as TX_FUENTEFINPRY   FROM ME_FINPYINV inner JOIN ME_METAFINPYINV ON ME_FINPYINV.NUM_FINPRY_PK=ME_METAFINPYINV.NUM_FINPRY_PK inner JOIN ME_PYINV ON ME_PYINV.NUM_PYINV_PK=ME_FINPYINV.NUM_PYINV_PK WHERE ME_PYINV.NUM_PYINV_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
        
            MarcoLogicoVO bean = null;
            while (rs.next()) {
                bean = new MarcoLogicoVO();
                setMarcoLogicoFinI(bean,rs);
                lista.add(bean);
            }
          

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

     public List getlistMarcoLogicoFinReporteI(int id, int anio) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MarcoLogicoVO> lista = new ArrayList<MarcoLogicoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            pstmt = conn.prepareStatement(sql);
            sql = "SELECT ME_PYINV.NUM_PYINV_PK, dbo.ME_FINPYINV.NUM_FINPRY_PK,isnull(dbo.ME_METAFINPYINV.NUM_METAFINPRY_PK,0) as NUM_METAFINPRY_PK,isnull(ME_FINPYINV.TX_DESCFINPRY,'') as TX_DESCFINPRY,isnull(ME_METAFINPYINV.NUM_CANTMETAFINPRY,0) as NUM_CANTMETAFINPRY,isnull(ME_METAFINPYINV.TX_DESCMETAFINPRY,'') as TX_DESCMETAFINPRY,isnull(ME_METAFINPYINV.TX_TIPOMETAFINPRY,0) as TX_TIPOMETAFINPRY,isnull(ME_METAFINPYINV.NUM_CANTMETAFINPRYC,0) as NUM_CANTMETAFINPRYC, isnull(ME_FINPYINV.TX_FUENTEFINPRY,'') as TX_FUENTEFINPRY   FROM ME_FINPYINV full outer JOIN ME_METAFINPYINV ON ME_FINPYINV.NUM_FINPRY_PK=ME_METAFINPYINV.NUM_FINPRY_PK full outer JOIN ME_PYINV ON ME_PYINV.NUM_PYINV_PK=ME_FINPYINV.NUM_PYINV_PK WHERE ME_PYINV.NUM_PYINV_PK=? AND YEAR( ME_PYINV.DT_INIPYI)=? ";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
             pstmt.setInt(2, anio);
            rs = pstmt.executeQuery();

            MarcoLogicoVO bean = null;
            while (rs.next()) {
                bean = new MarcoLogicoVO();
                setMarcoLogicoFinI(bean,rs);
                lista.add(bean);
            }


        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


    public List getlistMarcoLogicoProgramaI() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MarcoLogicoVO> lista = new ArrayList<MarcoLogicoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "SELECT ME_PYINV.NUM_PYINV_PK, ME_PYINV.TX_NOMBREPYI FROM ME_PYINV";
            rs = stmt.executeQuery(sql);
            MarcoLogicoVO bean = null;
            while (rs.next()) {
                bean = new MarcoLogicoVO();
                setMarcoLogicoProgramaI(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


    public List getlistMarcoLogicoPrograma() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MarcoLogicoVO> lista = new ArrayList<MarcoLogicoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "SELECT ME_PROGRAMA.NUM_CODPRG_PK, ME_PROGRAMA.TX_TITULOPRG FROM ME_PROGRAMA";
            rs = stmt.executeQuery(sql);
            MarcoLogicoVO bean = null;
            while (rs.next()) {
                bean = new MarcoLogicoVO();
                setMarcoLogicoPrograma(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


    public List getlistMarcoLogicoProposito() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MarcoLogicoVO> lista = new ArrayList<MarcoLogicoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "SELECT ME_METAPROPPRG.NUM_PROPPRG_PK,ME_METAPROPPRG.NUM_METAPROPPRG_PK,ME_PROPPRG.TX_DESCPROPPRG,ME_METAPROPPRG.NUM_CANTMETAPROPPRG,ME_METAPROPPRG.TX_DESCMETAPROPPRG,ME_METAPROPPRG.TX_TIPOMETAPROPPRG,ME_METAPROPPRG.NUM_CANTMETAPROPPRGC,ME_PROPPRG.TX_FUENTEPROPPRG FROM ME_PROPPRG INNER JOIN ME_METAPROPPRG ON ME_PROPPRG.NUM_PROPPRG_PK=ME_METAPROPPRG.NUM_PROPPRG_PK inner JOIN ME_PROGRAMA ON ME_PROGRAMA.NUM_CODPRG_PK=ME_PROPPRG.NUM_CODPRG_PK";
            rs = stmt.executeQuery(sql);
            MarcoLogicoVO bean = null;
            while (rs.next()) {
                bean = new MarcoLogicoVO();
                setMarcoLogicoProp(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }



    public List getlistMarcoLogicoProposito(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MarcoLogicoVO> lista = new ArrayList<MarcoLogicoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            pstmt = conn.prepareStatement(sql);
            sql = "SELECT ME_PROGRAMA.NUM_CODPRG_PK,ME_METAPROPPRG.NUM_PROPPRG_PK,ME_METAPROPPRG.NUM_METAPROPPRG_PK,ME_PROPPRG.TX_DESCPROPPRG,ME_METAPROPPRG.NUM_CANTMETAPROPPRG,ME_METAPROPPRG.TX_DESCMETAPROPPRG,ME_METAPROPPRG.TX_TIPOMETAPROPPRG,ME_METAPROPPRG.NUM_CANTMETAPROPPRGC,ME_PROPPRG.TX_FUENTEPROPPRG FROM ME_PROPPRG INNER JOIN ME_METAPROPPRG ON ME_PROPPRG.NUM_PROPPRG_PK=ME_METAPROPPRG.NUM_PROPPRG_PK inner JOIN ME_PROGRAMA ON ME_PROGRAMA.NUM_CODPRG_PK=ME_PROPPRG.NUM_CODPRG_PK WHERE ME_PROGRAMA.NUM_CODPRG_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            MarcoLogicoVO bean = null;
            while (rs.next()) {
                bean = new MarcoLogicoVO();
                setMarcoLogicoPropP(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

     public List getlistMarcoLogicoPropositoI(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MarcoLogicoVO> lista = new ArrayList<MarcoLogicoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            pstmt = conn.prepareStatement(sql);
            sql = "SELECT ME_PYINV.NUM_PYINV_PK,isnull(ME_METAPYINV.NUM_METAPROPPRY_PK,0) as NUM_METAPROPPRY_PK,isnull(dbo.ME_METAPYINV.NUM_PROPPYINV_PK,0) as NUM_PROPPYINV_PK,isnull(ME_PROPPYINV.TX_DESCPROPPRY,'') as TX_DESCPROPPRY,isnull(ME_METAPYINV.NUM_CANTMETAPROPPRY,0) as NUM_CANTMETAPROPPRY,isnull(ME_METAPYINV.TX_DESCMETAPROPPRY,'') as TX_DESCMETAPROPPRY,isnull(ME_METAPYINV.TX_TIPOMETAPROPPRY,0) as TX_TIPOMETAPROPPRY,isnull(ME_METAPYINV.NUM_CANTMETAPROPPRYC,0) as NUM_CANTMETAPROPPRYC,isnull(ME_PROPPYINV.TX_FUENTEPROPPRY,'') as TX_FUENTEPROPPRY FROM ME_PROPPYINV inner JOIN ME_METAPYINV ON ME_PROPPYINV.NUM_PROPPYINV_PK=ME_METAPYINV.NUM_PROPPYINV_PK inner JOIN ME_PYINV ON ME_PYINV.NUM_PYINV_PK=ME_PROPPYINV.NUM_PYINV_PK WHERE ME_PYINV.NUM_PYINV_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            rs = pstmt.executeQuery();
            MarcoLogicoVO bean = null;
            while (rs.next()) {
                bean = new MarcoLogicoVO();
                setMarcoLogicoPropI(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

 public List getlistMarcoLogicoPropositoReporteI(int id,int anio) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MarcoLogicoVO> lista = new ArrayList<MarcoLogicoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            pstmt = conn.prepareStatement(sql);
            sql = "SELECT ME_PYINV.NUM_PYINV_PK,ME_METAPYINV.NUM_METAPROPPRY_PK,isnull(dbo.ME_METAPYINV.NUM_PROPPYINV_PK,0) as NUM_PROPPYINV_PK,isnull(ME_PROPPYINV.TX_DESCPROPPRY,'') as TX_DESCPROPPRY,isnull(ME_METAPYINV.NUM_CANTMETAPROPPRY,0) as NUM_CANTMETAPROPPRY,isnull(ME_METAPYINV.TX_DESCMETAPROPPRY,'') as TX_DESCMETAPROPPRY,isnull(ME_METAPYINV.TX_TIPOMETAPROPPRY,0) as TX_TIPOMETAPROPPRY,isnull(ME_METAPYINV.NUM_CANTMETAPROPPRYC,0) as NUM_CANTMETAPROPPRYC,isnull(ME_PROPPYINV.TX_FUENTEPROPPRY,'') as TX_FUENTEPROPPRY FROM ME_PROPPYINV inner JOIN ME_METAPYINV ON ME_PROPPYINV.NUM_PROPPYINV_PK=ME_METAPYINV.NUM_PROPPYINV_PK inner JOIN ME_PYINV ON ME_PYINV.NUM_PYINV_PK=ME_PROPPYINV.NUM_PYINV_PK WHERE ME_PYINV.NUM_PYINV_PK=? AND YEAR( ME_PYINV.DT_INIPYI)=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.setInt(2, anio);
            rs = pstmt.executeQuery();
            MarcoLogicoVO bean = null;
            while (rs.next()) {
                bean = new MarcoLogicoVO();
                setMarcoLogicoPropI(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


    public List getlistMarcoLogicoComponente() throws SQLException {

        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MarcoLogicoVO> lista = new ArrayList<MarcoLogicoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            stmt = conn.createStatement();
            sql = "SELECT ME_METACOMPPRG.NUM_METACOMPPRG,dbo.ME_METACOMPPRG.NUM_COMPPRG_PK,ME_COMPPRG.TX_DESCCOMPPRG,ME_METACOMPPRG.NUM_CANTMETACOMPPRG,ME_METACOMPPRG.TX_DESCMETACOMPPRG,ME_METACOMPPRG.TX_TIPOMETACOMPPRG,ME_METACOMPPRG.NUM_CANTMETACOMPPRGC,ME_COMPPRG.TX_FUENTECOMPPRG FROM ME_COMPPRG INNER JOIN ME_METACOMPPRG ON ME_COMPPRG.NUM_COMPPRG_PK=ME_METACOMPPRG.NUM_COMPPRG_PK";
            rs = stmt.executeQuery(sql);
            MarcoLogicoVO bean = null;
            while (rs.next()) {
                bean = new MarcoLogicoVO();
                setMarcoLogicoComp(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }




    



    public List getlistMarcoLogicoComponenteI(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MarcoLogicoVO> lista = new ArrayList<MarcoLogicoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            pstmt = conn.prepareStatement(sql);
            sql = "SELECT ME_PYINV.NUM_PYINV_PK,isnull(ME_METACOMPPYINV.NUM_METACOMPPRY,0) as NUM_METACOMPPRY,isnull(dbo.ME_METACOMPPYINV.NUM_COMPPYINV_PK,0) as NUM_COMPPYINV_PK,isnull(ME_COMPPYINV.TX_DESCCOMPPYINV,'') as TX_DESCCOMPPYINV,isnull(ME_METACOMPPYINV.NUM_CANTMETACOMPPRY,0) as NUM_CANTMETACOMPPRY,isnull(ME_METACOMPPYINV.TX_DESCMETACOMPPRY,'') as TX_DESCMETACOMPPRY,isnull(ME_METACOMPPYINV.TX_TIPOMETACOMPPRY,0) as TX_TIPOMETACOMPPRY,isnull(ME_METACOMPPYINV.NUM_CANTMETACOMPPRYC,0) as NUM_CANTMETACOMPPRYC,isnull(ME_COMPPYINV.TX_FUENTECOMPPYINV,'') as TX_FUENTECOMPPYINV FROM ME_COMPPYINV inner JOIN ME_METACOMPPYINV ON ME_COMPPYINV.NUM_COMPPYINV_PK=ME_METACOMPPYINV.NUM_COMPPYINV_PK inner JOIN ME_PYINV ON ME_PYINV.NUM_PYINV_PK=ME_COMPPYINV.NUM_PYINV_PK WHERE ME_PYINV.NUM_PYINV_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);

            rs = pstmt.executeQuery();
            MarcoLogicoVO bean = null;
            while (rs.next()) {
                bean = new MarcoLogicoVO();
                setMarcoLogicoCompI(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }


    public List getlistMarcoLogicoComponente(int id) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MarcoLogicoVO> lista = new ArrayList<MarcoLogicoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            pstmt = conn.prepareStatement(sql);
            sql = "SELECT ME_PROGRAMA.NUM_CODPRG_PK,ME_METACOMPPRG.NUM_METACOMPPRG,dbo.ME_METACOMPPRG.NUM_COMPPRG_PK,ME_COMPPRG.TX_DESCCOMPPRG,ME_METACOMPPRG.NUM_CANTMETACOMPPRG,ME_METACOMPPRG.TX_DESCMETACOMPPRG,ME_METACOMPPRG.TX_TIPOMETACOMPPRG,ME_METACOMPPRG.NUM_CANTMETACOMPPRGC,ME_COMPPRG.TX_FUENTECOMPPRG FROM ME_COMPPRG INNER JOIN ME_METACOMPPRG ON ME_COMPPRG.NUM_COMPPRG_PK=ME_METACOMPPRG.NUM_COMPPRG_PK inner JOIN ME_PROGRAMA ON ME_PROGRAMA.NUM_CODPRG_PK=ME_COMPPRG.NUM_CODPRG_PK WHERE ME_PROGRAMA.NUM_CODPRG_PK=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);

            rs = pstmt.executeQuery();
            MarcoLogicoVO bean = null;
            while (rs.next()) {
                bean = new MarcoLogicoVO();
                setMarcoLogicoCompP(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }

     public List getlistMarcoLogicoComponenteReporteI(int id,int anio) throws SQLException {
        PreparedStatement pstmt = null;
        Statement stmt = null;
        ResultSet rs = null;
        ArrayList<MarcoLogicoVO> lista = new ArrayList<MarcoLogicoVO>(0);
        //lista=null;
        String sql = "";
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            pstmt = conn.prepareStatement(sql);
            sql = "SELECT ME_PYINV.NUM_PYINV_PK,isnull(ME_METACOMPPYINV.NUM_METACOMPPRY,0) as NUM_METACOMPPRY,dbo.ME_METACOMPPYINV.NUM_COMPPYINV_PK,isnull(ME_COMPPYINV.TX_DESCCOMPPYINV,'') as TX_DESCCOMPPYINV,isnull(ME_METACOMPPYINV.NUM_CANTMETACOMPPRY,0) as NUM_CANTMETACOMPPRY,isnull(ME_METACOMPPYINV.TX_DESCMETACOMPPRY,'') as TX_DESCMETACOMPPRY,isnull(ME_METACOMPPYINV.TX_TIPOMETACOMPPRY,0) as TX_TIPOMETACOMPPRY,isnull(ME_METACOMPPYINV.NUM_CANTMETACOMPPRYC,0) as NUM_CANTMETACOMPPRYC,isnull(ME_COMPPYINV.TX_FUENTECOMPPYINV,'') as TX_FUENTECOMPPYINV FROM ME_COMPPYINV inner JOIN ME_METACOMPPYINV ON ME_COMPPYINV.NUM_COMPPYINV_PK=ME_METACOMPPYINV.NUM_COMPPYINV_PK inner JOIN ME_PYINV ON ME_PYINV.NUM_PYINV_PK=ME_COMPPYINV.NUM_PYINV_PK WHERE ME_PYINV.NUM_PYINV_PK=? AND YEAR( ME_PYINV.DT_INIPYI)=?";
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, id);
            pstmt.setInt(2, anio);

            rs = pstmt.executeQuery();
            MarcoLogicoVO bean = null;
            while (rs.next()) {
                bean = new MarcoLogicoVO();
                setMarcoLogicoCompI(bean,rs);
                lista.add(bean);
            }

        } finally {
            if (rs != null) {
                rs.close();
                rs = null;
            }
            if (stmt != null) {
                stmt.close();
                stmt = null;
            }
            if (pstmt != null) {
                pstmt.close();
                pstmt = null;
            }
            if (conn != null) {
                conn.close();
                conn = null;
            }

        }
        return lista;
    }




    public String spMan(int tipo,MarcoLogicoVO bean) {



String msj = "";
        //JOptionPane.showMessageDialog(null, bean.getTX_TIPOFISIMETA());
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call ManLogicoCompMod(?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setDouble(2, bean.getNUM_CANTMETACOMPPRGC());
            cs.setInt(3, bean.getNUM_COMPPRG_PK());
            cs.setDouble(4, bean.getNUM_METACOMPPRG_PK());
            cs.execute();
        } catch (Exception e) {
       //JOptionPane.showMessageDialog(null, e.toString());
            return msj;
        }
        return msj;
    }


    public String spManI(int tipo,MarcoLogicoVO bean) {



String msj = "";
        //JOptionPane.showMessageDialog(null, bean.getTX_TIPOFISIMETA());
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call ManLogicoCompModI(?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setDouble(2, bean.getNUM_CANTMETACOMPPRGC());
            cs.setInt(3, bean.getNUM_COMPPRG_PK());
            cs.setDouble(4, bean.getNUM_METACOMPPRG_PK());
            cs.execute();
        } catch (Exception e) {
       //JOptionPane.showMessageDialog(null, e.toString());
            return msj;
        }
        return msj;
    }


    public String spManp(int tipo,MarcoLogicoVO bean) {



String msj = "";
        //JOptionPane.showMessageDialog(null, bean.getTX_TIPOFISIMETA());
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call ManLogicoPropMod(?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setDouble(2, bean.getNUM_CANTMETAPROPPRGC());
            cs.setInt(3, bean.getNUM_PROPPRG_PK());
            cs.setDouble(4, bean.getNUM_METAPROPPRG_PK());
            cs.execute();
        } catch (Exception e) {
       //JOptionPane.showMessageDialog(null, e.toString());
            return msj;
        }
        return msj;
    }


    public String spManpI(int tipo,MarcoLogicoVO bean) {



String msj = "";
        //JOptionPane.showMessageDialog(null, bean.getTX_TIPOFISIMETA());
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call ManLogicoPropModI(?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setDouble(2, bean.getNUM_CANTMETAPROPPRGC());
            cs.setInt(3, bean.getNUM_PROPPRG_PK());
            cs.setDouble(4, bean.getNUM_METAPROPPRG_PK());
            cs.execute();
        } catch (Exception e) {
       //JOptionPane.showMessageDialog(null, e.toString());
            return msj;
        }
        return msj;
    }


    public String spManf(int tipo,MarcoLogicoVO bean) {



String msj = "";
        //JOptionPane.showMessageDialog(null, bean.getTX_TIPOFISIMETA());
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call ManLogicoFinMod(?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setDouble(2, bean.getNUM_CANTMETAFINPRGC());
            cs.setInt(3, bean.getNUM_FINPRG_PK());
            cs.setDouble(4, bean.getNUM_METAFINPRG_PK());
            cs.execute();
        } catch (Exception e) {
       //JOptionPane.showMessageDialog(null, e.toString());
            return msj;
        }
        return msj;
    }



    public String spManfI(int tipo,MarcoLogicoVO bean) {



String msj = "";
        //JOptionPane.showMessageDialog(null, bean.getTX_TIPOFISIMETA());
        try {
            conn = DriverManager.getConnection(url, usuario, password);
            CallableStatement cs = conn.prepareCall("{call ManLogicoFinModI(?,?,?,?)}");
            cs.setInt(1, tipo);
            cs.setDouble(2, bean.getNUM_CANTMETAFINPRGC());
            cs.setInt(3, bean.getNUM_FINPRG_PK());
            cs.setDouble(4, bean.getNUM_METAFINPRG_PK());
            cs.execute();
        } catch (Exception e) {
       //JOptionPane.showMessageDialog(null, e.toString());
            return msj;
        }
        return msj;
    }




}


